#-*- coding:utf-8 -*-

'''
MySQL数据模型功能函数
author:cold night
email:wh_linux@126.com
'''

from MySQLdb import escape_string

def dict_to_sql(table, condition):
    key = ''
    value = ''
    for k, v in condition.items():
        if type(v) in [list, tuple]:
            continue

        key += "`%s`," % k
        value += "'%s'," % escape_string(v)
    return table + "("+key[0:-1]+")" + " VALUES(" + value[0:-1] + ")"


def dict_to_sql_update(table, condition):
    result = 'UPDATE %s SET ' % table
    for k, v in condition.items():
        if type(v) in [list, tuple, dict]:
            continue
        result += k+'="'+ escape_string(v) + '",'

    return result[0:-1] + ' '

def getcondfromdict(key, dict_val):
    '''
    将字典转换为sql语句
    '''
    result = ''
    i = 0
    for k,v in dict_val.items():
        i += 1
        v = str(v)
        if k == '$lt':
            result += "`%s`<'%s'" % (key, v)
        elif k == '$lte':
            result += "`%s`<='%s'" % (key, v)
        elif k == '$gt':
            result += "`%s`>'%s'" % (key, v)
        elif k == '$gte':
            result += "`%s`>='%s'" % (key, v)
        if i > 0 and i < len(dict_val.items()):
            result += ' and '
        
    return result

def dict_to_sql_select(table, condition, field='*'):
    '''
    `field`: 查询的字段
    '''
    result = 'select %s from %s ' % (field,table)
    if not condition: return result
    else: result += ' where '
    i = 0

    for k, v in condition.items():
        i += 1
        if type(v) in [list, tuple]:
            continue
        elif type(v) == dict:
            result += getcondfromdict(k,v)
        else:
            result += "`%s`='%s'" % (k, v)
        if i > 0 and i < len(condition.items()):
            result += ' and '
        elif i == len(condition.items()):
            result += ' '
        
    return result


def format_result(result, keys, **kargs):
    '''
    将sql结果转换为字典
    `result`: sql结果,
    `keys`:sql结果对应的key
    keys和结果顺序要一一对应
    '''
    r = {}
    if len(result) == len(keys):
        i=0
        for key in keys:
            r[key] = result[i]
            i+=1
    else:
        raise ValueError("key's length not equal result's length")
    r.update(kargs)
    return r
